from pymysql import MySQLError

from utils.ConfigUtils import read_config
import pymysql



def query_data(sql):
    """
    执行MySQL插入操作
    :param config: 数据库配置字典
    :param data: 要插入的数据元组/列表
    :return: 插入的行数
    """
    connection = None
    mysqlConfig = read_config("config/jdbc.properties")
    try:
        # 1. 建立连接
        connection = pymysql.connect(
            host=mysqlConfig.get("mysql_host"),
            user=mysqlConfig.get("mysql_user"),
            password=mysqlConfig.get("mysql_password"),
            database=mysqlConfig.get("mysql_db"),
            port=int(mysqlConfig.get("mysql_port")),
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        # 2. 准备SQL（使用参数化查询防止SQL注入）
        # sql = """
        #       INSERT INTO traffic_flow
        #           (road_id, flow_count, record_time)
        #       VALUES (%s, %s, %s) \
        #       """

        # 3. 执行插入
        with connection.cursor() as cursor:
            cursor.executemany(sql, data)

        # 4. 提交事务
        connection.commit()
        return cursor.rowcount

    except MySQLError as e:
        print(f"数据库错误: {e}")
        if connection:
            connection.rollback()
        return 0
    finally:
        if connection:
            connection.close()


def insert_data_to_mysql(sql, data):
    """
    执行MySQL插入操作
    :param config: 数据库配置字典
    :param data: 要插入的数据元组/列表
    :return: 插入的行数
    """
    connection = None
    mysqlConfig = read_config("config/jdbc.properties")
    try:
        # 1. 建立连接
        connection = pymysql.connect(
            host=mysqlConfig.get("mysql_host"),
            user=mysqlConfig.get("mysql_user"),
            password=mysqlConfig.get("mysql_password"),
            database=mysqlConfig.get("mysql_db"),
            port=int(mysqlConfig.get("mysql_port")),
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        # 2. 准备SQL（使用参数化查询防止SQL注入）
        # sql = """
        #       INSERT INTO traffic_flow
        #           (road_id, flow_count, record_time)
        #       VALUES (%s, %s, %s) \
        #       """

        # 3. 执行插入
        with connection.cursor() as cursor:
            cursor.executemany(sql, data)

        # 4. 提交事务
        connection.commit()
        return cursor.rowcount

    except MySQLError as e:
        print(f"数据库错误: {e}")
        if connection:
            connection.rollback()
        return 0
    finally:
        if connection:
            connection.close()